package com.zxy.ziems.server.statistics.util;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.merge.LoopMergeStrategy;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.zxy.ziems.server.constant.DateType;
import com.zxy.ziems.server.statistics.pojo.vo.HomologyChainBodyVO;
import com.zxy.ziems.server.statistics.pojo.vo.HomologyChainVO;
import jakarta.servlet.ServletOutputStream;
import jakarta.servlet.http.HttpServletResponse;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.core.io.ClassPathResource;

import java.io.IOException;
import java.io.InputStream;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @Copyright (C),
 * @ClassName: WetDryExcelExportUtil
 * @Author: yanzhiwei
 * @Description:
 * @Date: 2024/8/26 13:46
 */
@Slf4j
public class HomologyChainExportUtil {

    private Integer jiaojiebanrizhiSheet = 0;


    public void complexFillWithTable1(HttpServletResponse response, HomologyChainVO homologyChainVO) {
        String filename = "同比.xlsx";

        // 确保文件名编码正确并且转义了特殊字符
        String encodedFileName = null;
        try {
            encodedFileName = URLEncoder.encode(filename, "UTF-8").replaceAll("\\+", "%20");
        } catch (UnsupportedEncodingException e) {
            throw new RuntimeException(e);
        }
        response.setHeader("Content-Disposition", "attachment; filename=\"" + encodedFileName + "\"");


        response.setContentType("application/vnd.ms-excel");


        ClassPathResource classPathResource = new ClassPathResource("template/HomologyChain.xlsx");

        // 这里 会填充到第一个sheet， 然后文件流会自动关闭
        try (ServletOutputStream outputStream = response.getOutputStream(); InputStream inputStream = classPathResource.getInputStream()) {
//            String fileName = getGenPath();
//            ExcelWriter excelWriter = EasyExcel.write(fileName).withTemplate(inputStream).build();
            ExcelWriter excelWriter = EasyExcel.write(outputStream).withTemplate(inputStream).build();
//
            genExcel1(excelWriter, homologyChainVO);


            Workbook workbook = excelWriter.writeContext().writeWorkbookHolder().getWorkbook();
            //设置强制计算公式：不然公式会以字符串的形式显示在excel中
            workbook.setForceFormulaRecalculation(true);


            excelWriter.finish();
        } catch (Exception e) {
            log.error("exportByTemplate-导出失败", e);
        }


    }


    /**
     * @param response
     * @param homologyChainVO
     */
    public void complexFillWithTable2(HttpServletResponse response, HomologyChainVO homologyChainVO, String queryType) {

        String filename = "环比.xlsx";

        // 确保文件名编码正确并且转义了特殊字符
        String encodedFileName = null;
        try {
            encodedFileName = URLEncoder.encode(filename, "UTF-8").replaceAll("\\+", "%20");
        } catch (UnsupportedEncodingException e) {
            throw new RuntimeException(e);
        }
        response.setHeader("Content-Disposition", "attachment; filename=\"" + encodedFileName + "\"");


        response.setContentType("application/vnd.ms-excel");

        // 模板注意 用{} 来表示你要用的变量 如果本来就有"{","}" 特殊字符 用"\{","\}"代替
        // {} 代表普通变量 {.} 代表是list的变量
        // 这里模板 删除了list以后的数据，也就是统计的这一行


        ClassPathResource classPathResource = new ClassPathResource("template/HomologyChainHuan.xlsx");

        // 这里 会填充到第一个sheet， 然后文件流会自动关闭
        try (ServletOutputStream outputStream = response.getOutputStream(); InputStream inputStream = classPathResource.getInputStream()) {

            ExcelWriter excelWriter = EasyExcel.write(outputStream).withTemplate(inputStream)
                    .build();
//
            genExcel2(excelWriter, homologyChainVO, queryType);


            Workbook workbook = excelWriter.writeContext().writeWorkbookHolder().getWorkbook();
            //设置强制计算公式：不然公式会以字符串的形式显示在excel中
            workbook.setForceFormulaRecalculation(true);


            // 处理格式
            dealWork(workbook, homologyChainVO, outputStream);


            excelWriter.finish();
        } catch (Exception e) {
            log.error("exportByTemplate-导出失败", e);
        }


    }

    private void dealWork(Workbook workbook, HomologyChainVO homologyChainVO, ServletOutputStream outputStream) throws IOException {
        Sheet sheet = workbook.getSheetAt(0);

        int size = homologyChainVO.getHeaders().size();

        int lastRowNum = sheet.getLastRowNum();
        for (int j = 2; j <= lastRowNum; j++) {
            if (j % 2 == 1) {
                for (int i = 0; i < size; i++) {
                    int lie = 2 * i;
                    CellRangeAddress cellRangeAddress = new CellRangeAddress(j, j, 2 + lie, 3 + lie);
                    sheet.addMergedRegion(cellRangeAddress);
                }
            }
        }


        workbook.write(outputStream);
        outputStream.close();
        workbook.close();
        workbook.close(); // 关闭工作簿资源
    }

    private void genExcel2(ExcelWriter excelWriter, HomologyChainVO homologyChainVO, String queryType) {

        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
        contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
        contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
        contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
        //设置背景颜色
        contentWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE1.getIndex());
        //设置填充模式
        contentWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
        contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);


        HorizontalCellStyleStrategy horizontalCellStyleStrategy =
                new HorizontalCellStyleStrategy(contentWriteCellStyle, contentWriteCellStyle);

        // 在循环数据中 每2行合并一次 从第1列开始合并1列
        LoopMergeStrategy loopMergeStrategy2 = new LoopMergeStrategy(2, 1, 0);


        Map<String, Object> map = data1(homologyChainVO);
        data3(homologyChainVO, map, queryType);
        List<Map<String, String>> list2 = data4(homologyChainVO);


        // 直接写入数据


        WriteSheet writeSheet = EasyExcel.writerSheet(jiaojiebanrizhiSheet).
                registerWriteHandler(loopMergeStrategy2).
                registerWriteHandler(horizontalCellStyleStrategy).
                build();


        excelWriter.fill(map, writeSheet);
        excelWriter.fill(list2, writeSheet);


    }

    private void data3(HomologyChainVO homologyChainVO, Map<String, Object> map, String queryType) {

        String key = "周";
        switch (queryType) {
            case DateType.MONTH: {
                key = "月";
                break;
            }
            case DateType.YEAR: {
                key = "年";
                break;
            }
            default: {
                key = "周";
            }
        }


        homologyChainVO.getHeaders();
        // 表头
        List<String> headers = homologyChainVO.getHeaders();
        for (Integer i = 0; i < headers.size(); i++) {
            map.put("A_" + (i + 2) + "_1", "本" + key);
            map.put("A_" + (i + 2) + "_2", "上" + key);
        }

    }


    private List<Map<String, String>> genExcel1(ExcelWriter excelWriter, HomologyChainVO homologyChainVO) {

        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
        contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
        contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
        contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
        //设置背景颜色
        contentWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE1.getIndex());
        //设置填充模式
        contentWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
        contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);


        HorizontalCellStyleStrategy horizontalCellStyleStrategy =
                new HorizontalCellStyleStrategy(contentWriteCellStyle, contentWriteCellStyle);

        // 在循环数据中 每2行合并一次 从第1列开始合并1列
        LoopMergeStrategy loopMergeStrategy2 = new LoopMergeStrategy(2, 1, 0);


        Map<String, Object> map = data1(homologyChainVO);
        List<Map<String, String>> list2 = data2(homologyChainVO);


        // 直接写入数据


        WriteSheet writeSheet = EasyExcel.writerSheet(jiaojiebanrizhiSheet).
                registerWriteHandler(loopMergeStrategy2).
                registerWriteHandler(horizontalCellStyleStrategy).
                build();

        excelWriter.fill(map, writeSheet);
        excelWriter.fill(list2, writeSheet);


        return null;
    }


    private Map<String, Object> data1(HomologyChainVO homologyChainVO) {

        // 表头
        List<String> headers = homologyChainVO.getHeaders();
        Map<String, Object> m = new HashMap<String, Object>();
        m.put("A0", "数据名称");
        m.put("A1", "类型");
        for (Integer i = 0; i < headers.size(); i++) {
            String s = headers.get(i);
            m.put("A" + (i + 2), s);
        }

        return m;
    }

    private List<Map<String, String>> data2(HomologyChainVO homologyChainVO) {
        List<Map<String, String>> list = new ArrayList<>();
        List<String> headers = homologyChainVO.getHeaders();

        // 表格
        List<HomologyChainBodyVO> homologyChainBodyVOList = homologyChainVO.getBodyList();
        for (HomologyChainBodyVO homologyChainBodyVO : homologyChainBodyVOList) {
            Map map1 = new HashMap<String, String>();
            Map map2 = new HashMap<String, String>();

            map1.put("B0", homologyChainBodyVO.getName());
            map1.put("B1", "数值");
            map2.put("B0", homologyChainBodyVO.getName());
            map2.put("B1", "同比");


            Map<String, Object> curData = homologyChainBodyVO.getCurData();
            Map<String, Object> radioData = homologyChainBodyVO.getRadio();


            for (Integer i = 0; i < headers.size(); i++) {
                String s = headers.get(i);
                Object o1 = curData.get(s);
                Object o2 = radioData.get(s);
                map1.put("B" + (i + 2), o1);
                map2.put("B" + (i + 2), o2.equals("") ? "--" : o2);
            }
            list.add(map1);
            list.add(map2);
        }
        return list;
    }

    private List<Map<String, String>> data4(HomologyChainVO homologyChainVO) {
        List<Map<String, String>> list = new ArrayList<>();
        List<String> headers = homologyChainVO.getHeaders();

        // 表格
        List<HomologyChainBodyVO> homologyChainBodyVOList = homologyChainVO.getBodyList();
        for (HomologyChainBodyVO homologyChainBodyVO : homologyChainBodyVOList) {
            Map map1 = new HashMap<String, String>();
            Map map2 = new HashMap<String, String>();

            map1.put("B0", homologyChainBodyVO.getName());
            map1.put("B1", "数值");
            map2.put("B0", homologyChainBodyVO.getName());
            map2.put("B1", "环比");


            Map<String, Object> curData = homologyChainBodyVO.getCurData();
            Map<String, Object> oldData = homologyChainBodyVO.getOldData();
            Map<String, Object> radioData = homologyChainBodyVO.getRadio();


            for (Integer i = 0; i < headers.size(); i++) {
                String s = headers.get(i);
                Object o1 = curData.get(s);
                Object o2 = oldData.get(s);
                Object o3 = radioData.get(s);
                map1.put("B_" + (i + 2) + "_1", o1);
                map1.put("B_" + (i + 2) + "_2", o2);
                map2.put("B_" + (i + 2) + "_1", o3.equals("") ? "--" : o3);
                map2.put("B_" + (i + 2) + "_2", "");
            }
            list.add(map1);
            list.add(map2);
        }
        return list;
    }


}
